package com.vikutorika.dao.imp;

import cn.hutool.core.util.StrUtil;
import com.vikutorika.dao.BasicDAO;
import com.vikutorika.dao.FriendRelationDAO;
import com.vikutorika.entity.FriendRelation;
import com.vikutorika.entity.UserInfo;
import com.vikutorika.vo.FriendVo;
import com.vikutorika.util.V_DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class FriendRelationDAOImp extends BasicDAO<FriendRelation> implements FriendRelationDAO {
	@Override
	public List<FriendRelation> getAll() {
		Connection conn = null;
		List<FriendRelation> list;
		String sql = "select * from friend_relation;";
		try {
			conn = V_DBUtils.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			list = queryRunner.query(conn, sql, new BeanListHandler<>(FriendRelation.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			V_DBUtils.close(conn);
		}
		return list;
	}
	public List<UserInfo> listMyFriends(Integer myUid) {
		Connection conn = null;
		List<UserInfo> list;
		String sql = "select user_info.* " +
				"from user_info left join (select CASE WHEN uid!=? then uid " +
				"WHEN friend_uid!=? then friend_uid " +
				"END as 'tuid' " +
				"from friend_relation " +
				"where uid=? or friend_uid=?) t1 on tuid=uid " +
				"where tuid is not NULL;";
		try {
			conn = V_DBUtils.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			list = queryRunner.query(conn, sql, new BeanListHandler<>(UserInfo.class),
					myUid, myUid, myUid, myUid
			);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			V_DBUtils.close(conn);
		}
		return list;
	}
	@Override
	public int totalRow() {
		Connection conn = null;
		int res;
		String sql = "select count(*) from friend_relation;";
		try {
			conn = V_DBUtils.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			res = queryRunner.query(conn, sql, new ScalarHandler<>());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			V_DBUtils.close(conn);
		}
		return res;
	}

	/**
	 * 获取好友UID
	 */
	@Override
	public FriendRelation getByUID(Integer uid) {
		String sql = "select * from friend_relation where uid=?;";
		Connection conn = null;
		FriendRelation fr = null;
		try {
			conn = V_DBUtils.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			fr = queryRunner.query(conn, sql, new BeanHandler<>(FriendRelation.class), uid, uid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			V_DBUtils.close(conn);
		}
		return fr;
	}

	/**
	 * 获取好友UID
	 */
	@Override
	public FriendRelation getByFriendUID(Integer uid) {
		String sql = "select * from friend_relation where friend_uid=?;";
		Connection conn = null;
		FriendRelation fr = null;
		try {
			conn = V_DBUtils.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			fr = queryRunner.query(conn, sql, new BeanHandler<>(FriendRelation.class), uid, uid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			V_DBUtils.close(conn);
		}
		return fr;
	}
	@Override
	public List<FriendRelation> getByState(String state) {
		Connection conn = null;
		List<FriendRelation> list;
		String sql = "select * from friend_relation where state=?;";
		try {
			conn = V_DBUtils.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			list = queryRunner.query(conn, sql, new BeanListHandler<>(FriendRelation.class), state);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			V_DBUtils.close(conn);
		}
		return list;
	}

	public List<FriendRelation> query(FriendVo friendVo) {
		Connection conn = null;
		List<FriendRelation> list;
		List<String> params = new LinkedList<>();
		StringBuilder sql = new StringBuilder("select * from user_info where 1=1");
		if (StrUtil.isNotBlank(friendVo.getUid())){
			sql.append(" and uid=?");
			params.add(friendVo.getUid());
		}
		if (StrUtil.isNotBlank(friendVo.getName())){
			sql.append(" and nickname like concat('%',?,'%')");
			params.add(friendVo.getName());
		}
		if (StrUtil.isNotBlank(friendVo.getGender())){
			sql.append(" and gender=?");
			params.add(friendVo.getGender());
		}
		try {
			conn = V_DBUtils.getConnection();
			QueryRunner queryRunner = new QueryRunner();
			list = queryRunner.query(conn, sql.toString(), new BeanListHandler<>(FriendRelation.class), params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			V_DBUtils.close(conn);
		}
		return list;
	}
}
